package com.neusoft.chenhanhua.gqing;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Test;

public class Oms_order_operate_history17 {
	//插入
		@Test
		public void insert() {
			Connection conn=null;
			//准备数据库用户名，密码，url，driver
			String username="root";//用户名
			String password="root";//密码
			String driver="com.mysql.jdbc.Driver";//driver
			String url="jdbc:mysql://localhost:3306/mall?useUnicode=true&characterEncoding=utf-8";//url
			try {
				//加载驱动类到虚拟机中
				Class.forName(driver);//这是属于反射的内容
				//获取连接java.sql.Connection
				conn=DriverManager.getConnection(url,username,password);
			} catch (Exception e) {
				e.printStackTrace();
			}
			//准备SQL命令
			String sql="insert into Oms_order_operate_history(id,order_id,operate_man,create_time,order_status,note)values(2,9,2,'2016-09-07',5,6)";
			Statement st=null;
			try {
				//通过数据库连接conn获取事务java.sql.Statement对象st
				st=conn.createStatement();
				//执行sql命令
				int row=st.executeUpdate(sql);
				System.out.println(row>0?"添加成功":"添加失败");
			} catch (SQLException e) {
				e.printStackTrace();
			}finally {
				if (st!=null) {
					try {
						st.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				if (conn!=null) {
					try {
						st.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
		}
		@Test 
		public void update() {
			Connection conn=null;
			//准备数据库用户名,密码，url，driver
			String username="root";
			String password="root";
			String driver="com.mysql.jdbc.Driver";
			String url="jdbc:mysql://localhost:3306/mall?useUnicode=true&characterEncoding=utf-8";
			try {
				//加载驱动类到虚拟机中
				Class.forName(driver);
				//获取数据库连接java.sql.Connection
				conn=DriverManager.getConnection(url,username,password);
			} catch (Exception e) {
				e.printStackTrace();
			}
			//准备SQL命令
			String sql="update Oms_order_operate_history set order_id=5 where id=2";
			Statement st=null;
			try {
				//通过数据库连接conn获取事务java.sql.staement
				st=conn.createStatement();
				int row=st.executeUpdate(sql);
				System.out.println(row>0?"修改成功":"修改失败");
			} catch (SQLException e) {
				e.printStackTrace();
			}finally {
				if (st!=null) {
					try {
						st.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				if (conn!=null) {
					try {
						st.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
			
		}
		@Test
		public void delete() {
			Connection conn=null;
			//准备数据库用户名，密码，url，driver
			String username="root";
			String password="root";
			String driver="com.mysql.jdbc.Driver";
			//协议：//地址：端口号/数据库？连接属性设置
			String url="jdbc:mysql://localhost:3306/mall?useUnicode=true&characterEncoding=utf-8";
			try {
				//加载驱动类到虚拟机中，这是属于反射的内容
				Class.forName(driver);
				//获取数据库连接java.sql.Connection
				conn=DriverManager.getConnection(url,username,password);
			} catch (Exception e) {
				e.printStackTrace();
			}
			//准备SQL命令
			String sql="delete from Oms_order_operate_history where id=1";
			Statement st=null;
			try {
				//通过数据库连接conn获取事务java.sql.Statement对象st
				st=conn.createStatement();
				//执行sql命令
				int row=st.executeUpdate(sql);
				System.out.println(row>0?"删除成功":"删除失败");
			} catch (SQLException e) {
				e.printStackTrace();
			}finally {
				if (st!=null) {
					try {
						st.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				if (conn!=null) {
					try {
						st.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
		}
		@Test
		public void selet() {
			Connection conn=null;
			String username="root";
			String password="root";
			String driver="com.mysql.jdbc.Driver";
			String url="jdbc:mysql://localhost:3306/mall?useUnicode=true&characterEncoding=utf-8";
			try {
				//2.加载驱动到虚拟机中
				Class.forName(driver);//这个是反射
				//DriverManager数据库连接
				conn=DriverManager.getConnection(url,username,password);
			} catch (Exception e) {
				e.printStackTrace();
			}
			//准备SQL命令
			String sql="select * from Oms_order_operate_history";
			Statement st=null;
			ResultSet rs=null;
			try {
				//通过数据库连接conn获取事务java.sql.Statement对象st
				st=conn.createStatement();
				//执行sql命令
				rs=st.executeQuery(sql);
				//处理结果集
				while (rs.next()) {
					//通过columnLabel列名或者别名获取,
					System.out.println(rs.getInt("id"));
					System.out.println(rs.getObject("order_id"));
					System.out.println(rs.getObject("operate_man"));
					System.out.println(rs.getObject("create_time"));
					System.out.println(rs.getObject("order_status"));
					System.out.println(rs.getObject("note"));
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}finally {
				if (st!=null) {
					try {
						st.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				if (rs!=null) {
					try {
						rs.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				if (conn!=null) {
					try {
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
			
			
		}

}
